package orm

import (
	"encoding/json"
	"errors"
	"fmt"
	"gitee.com/wuzheng0709/backend-gopkg/infrastructure/pkg/gin/log"
	xml2 "gitee.com/wuzheng0709/backend-gopkg/infrastructure/pkg/xml"
	"github.com/alecthomas/jsonschema"
	"gorm.io/gorm"
	"reflect"
	"strings"
	"time"
)

const (
	SqlServertype = iota + 1
	MysqlType
)

type CommonModel struct {
	ID        int64          `gorm:"column:id;primary_key;AUTO_INCREMENT" form:"id"   json:"id"`
	CreatedAt time.Time      `gorm:"column:createdAt" form:"createdAt"  json:"createdAt"`
	UpdatedAt time.Time      `gorm:"column:updatedAt" form:"updatedAt"  json:"updatedAt"`
	DeletedAt gorm.DeletedAt `gorm:"column:deletedAt" form:"deletedAt"  sql:"index" json:"deletedAt"`
}

type CommonModelV1 struct {
	ID        int64          `gorm:"column:id;primary_key;AUTO_INCREMENT" form:"id"   json:"id"`
	CreatedAt time.Time      `gorm:"column:created_at" form:"created_at"  json:"created_at"`
	UpdatedAt time.Time      `gorm:"column:updated_at" form:"updated_at"  json:"updated_at"`
	DeletedAt gorm.DeletedAt `gorm:"column:deleted_at" form:"deleted_at"  sql:"index" json:"deleted_at"`
}

type GinModel struct {
	ID        int64          `gorm:"column:id;primary_key;AUTO_INCREMENT" form:"id"   json:"id"`
	CreatedAt time.Time      `gorm:"column:created_at" form:"createdAt"  json:"createdAt"`
	UpdatedAt time.Time      `gorm:"column:updated_at" form:"updatedAt"  json:"updatedAt"`
	DeletedAt gorm.DeletedAt `gorm:"column:deleted_at" form:"deletedAt"  sql:"index" json:"deletedAt"`
}

// 分页条件
type PageWhere struct {
	Where string
	Value []interface{}
}

// 分页参数返回
type IndexPage struct {
	Total int `json:"total"`  //总数
	Page  int `json:"page"  ` //页数
	Num   int `json:"num"   ` //数量
}

// Create
func Create(db *gorm.DB, value interface{}) error {
	return db.Create(value).Error
}

// Save
func Save(db *gorm.DB, value interface{}) error {
	return db.Save(value).Error
}

// Updates
func Updates(db *gorm.DB, where interface{}, value interface{}) error {
	return db.Model(where).Updates(value).Error
}

// Delete
func DeleteByModel(db *gorm.DB, model interface{}) (count int64, err error) {
	db = db.Delete(model)
	err = db.Error
	if err != nil {
		return
	}
	count = db.RowsAffected
	return
}

// Delete
func DeleteByWhere(db *gorm.DB, model, where interface{}) (count int64, err error) {
	db = db.Where(where).Delete(model)
	err = db.Error
	if err != nil {
		return
	}
	count = db.RowsAffected
	return
}

// Delete
func DeleteByWhereTx(db *gorm.DB, model, where interface{}) (count int64, err error, tx *gorm.DB) {
	db = db.Where(where).Delete(model)
	err = db.Error
	if err != nil {
		return
	}
	count = db.RowsAffected
	return
}

// Delete
func DeleteByID(db *gorm.DB, model interface{}, id string) (count int64, err error) {
	db = db.Where("id=?", id).Delete(model)
	err = db.Error
	if err != nil {
		return
	}
	count = db.RowsAffected
	return
}

// Delete
func DeleteByIDS(db *gorm.DB, model interface{}, ids []string) (count int64, err error) {
	db = db.Where("id in (?)", ids).Delete(model)
	err = db.Error
	if err != nil {
		return
	}
	count = db.RowsAffected
	return
}

// First
func FirstByID(db *gorm.DB, out interface{}, id string) (notFound bool, err error) {
	err = db.First(out, id).Error
	if err != nil {
		notFound = errors.Is(err, gorm.ErrRecordNotFound)
	}
	return
}

// First
func First(db *gorm.DB, where interface{}, out interface{}) (notFound bool, err error) {
	err = db.Where(where).First(out).Error
	if err != nil {
		notFound = errors.Is(err, gorm.ErrRecordNotFound)
	}
	return
}

// Find
func Find(db *gorm.DB, where interface{}, out interface{}, orders ...string) error {
	db = db.Where(where)
	if len(orders) > 0 {
		for _, order := range orders {
			db = db.Order(order)
		}
	}
	return db.Find(out).Error
}

// Scan
func Scan(db *gorm.DB, model, where interface{}, out interface{}) (notFound bool, err error) {
	err = db.Model(model).Where(where).Scan(out).Error
	if err != nil {
		notFound = errors.Is(err, gorm.ErrRecordNotFound)
	}
	return
}

// ScanList
func ScanList(db *gorm.DB, model, where interface{}, out interface{}, orders ...string) error {
	db = db.Model(model).Where(where)
	if len(orders) > 0 {
		for _, order := range orders {
			db = db.Order(order)
		}
	}
	return db.Scan(out).Error
}

// out 指针类型
func SelectByWhere(db *gorm.DB, whereMap map[string]string, columnList []string, out interface{}) error {
	return db.Select(columnList).Model(out).Where(whereMap).Scan(out).Error
}

// GetPage
func GetPage(db *gorm.DB, model, where interface{}, out interface{}, indexPage *IndexPage, order string, whereOrder ...PageWhere) error {
	db = db.Model(model).Where(where)
	if order != "" {
		db = db.Order(order)
	}
	if len(whereOrder) > 0 {
		for _, wo := range whereOrder {
			if wo.Where != "" {
				db = db.Where(wo.Where, wo.Value...)
			}
		}
	}
	var num int64
	err := db.Count(&num).Error
	if err != nil {
		return err
	}
	indexPage.Total = int(num)
	if indexPage.Total == 0 {
		return nil
	}
	return db.Offset((indexPage.Page - 1) * indexPage.Num).Limit(indexPage.Num).Scan(out).Error
}

func GetPageWithJoin(db *gorm.DB, model interface{}, fields string, where interface{}, out interface{}, indexPage *IndexPage, order string, join string, whereOrder ...PageWhere) error {
	db = db.Model(model).Where(where)
	if join != "" {
		db = db.Joins(join)
	}
	if order != "" {
		db = db.Order(order)
	}

	if len(whereOrder) > 0 {
		for _, wo := range whereOrder {
			if wo.Where != "" {
				db = db.Where(wo.Where, wo.Value...)
			}
		}
	}

	db = db.Select(fields)

	var num int64
	err := db.Count(&num).Error
	if err != nil {
		return err
	}
	indexPage.Total = int(num)
	if indexPage.Total == 0 {
		return nil
	}

	return db.Offset((indexPage.Page - 1) * indexPage.Num).Limit(indexPage.Num).Scan(out).Error
}

// GetPage
func GetPageToMap(db *gorm.DB, tableName string, out interface{}, indexPage *IndexPage, order string, whereOrder ...PageWhere) error {
	db = db.Table(tableName)
	if order != "" {
		db = db.Order(order)
	}
	db = db.Where("deleted_at IS NULL")
	if len(whereOrder) > 0 {
		for _, wo := range whereOrder {
			if wo.Where != "" {
				db = db.Where(wo.Where, wo.Value...)
			}
		}
	}
	var num int64
	err := db.Count(&num).Error
	if err != nil {
		return err
	}
	indexPage.Total = int(num)
	if indexPage.Total == 0 {
		return nil
	}
	return db.Offset((indexPage.Page - 1) * indexPage.Num).Limit(indexPage.Num).Scan(out).Error
}

// PluckList
func PluckList(db *gorm.DB, model, where interface{}, fieldName string, out interface{}) error {
	return db.Model(model).Where(where).Pluck(fieldName, out).Error
}

type DictionaryInfo struct {
	ColumnName    string `json:"column_name"`    // 字段名称
	DataType      string `json:"data_type"`      // 类型
	IsNullable    string `json:"is_nullable"`    // 是否为空
	ColumnComment string `json:"column_comment"` // 描述
}
type TableInfo struct {
	CreateTime   string `json:"create_time"`   // 创建时间
	TableName    string `json:"table_name"`    // 表名
	TableComment string `json:"table_comment"` // 表中文名
}

func GetColumnNameList(db *gorm.DB, DBName, TableName string, out *[]DictionaryInfo) error {
	sql := `select column_name ,data_type,is_nullable ,column_comment  from information_schema.columns where  column_comment!="" and  table_schema  = ? and table_name = ?`
	return db.Raw(sql, DBName, TableName).Scan(out).Error
}

func GetDbTableList(db *gorm.DB, DBName string, out *[]TableInfo) error {
	sql := "select create_time,table_name,table_comment from information_schema.tables where table_schema= ? and table_comment!=''"
	return db.Raw(sql, DBName).Scan(out).Error
}
func test() {
	schema := jsonschema.Reflect(&CommonModel{})
	schemajson, _ := json.Marshal(schema)
	fmt.Println(string(schemajson))
	return
}

func GetUserTypeByAppUserId(db *gorm.DB, appId int, out interface{}) error {
	sql := "select group_concat(user_type separator ',') as user_types from application_user_org where app_user_id = ?  group by app_user_id"
	return db.Raw(sql, appId).Scan(out).Error
}

func GetUserIdsByAdminAllDelartUser(db *gorm.DB, orgId int, out interface{}) error {
	sql := "select org_id, full_name, name, group_concat(user_id separator ',') as users from admin_all_depart_user where org_id = ?  group by org_id"
	return db.Raw(sql, orgId).Scan(out).Error
}

// Like
func LikeStr(keyStr string) string {
	// sql like 验证
	//keyStr = strings.Replace(keyStr, "\\", "\\\\", -1)
	keyStr = strings.Replace(keyStr, "%", "\\%", -1)
	return "%" + keyStr + "%"

}

// 数据存在更新，不存在创建
// 根据条件判断数据是否存在是否存在
// 存在 创建
// 不存在 更新指定字段 true:数据存在，false:数据不存在
func CreateOrUpdate(db *gorm.DB, tableName string, MapFieldValue map[string]interface{}, query interface{}, args ...interface{}) error {
	var countNum int64
	if err := db.Table(tableName).Where(query, args...).Count(&countNum).Error; err != nil {
		return err
	}
	if countNum > 0 {
		// 更新
		if err := db.Table(tableName).Where(query, args...).Updates(MapFieldValue).Error; err != nil {
			return err
		}
		return nil
	} else {
		if err := db.Table(tableName).Create(MapFieldValue).Error; err != nil {
			return err
		}
		return nil
	}
	return nil
}

// 数据存在更新，不存在创建
// 根据条件判断数据是否存在是否存在
// 存在 创建
func CreateOrUpdatev1[T any](db *gorm.DB, stru *T, query interface{}, args ...interface{}) error {
	var firstStru T
	if err := db.Model(firstStru).Where(query, args...).First(&firstStru).Error; err != nil {
		if err == gorm.ErrRecordNotFound {
			if err = db.Model(firstStru).Create(stru).Error; err != nil {
				return err
			}
		}
		return err
	} else {
		if err = db.Model(firstStru).Where(query, args...).Updates(stru).Error; err != nil {
			return err
		}
	}
	return db.Model(firstStru).Where(query, args...).First(stru).Error
}

// 特殊处理：更新map中不存在的字段
// 数据存在更新，不存在创建
// 根据条件判断数据是否存在是否存在
// 存在 创建
// 不存在 更新指定字段 true:数据存在，false:数据不存在
func CreateOrUpdateOfSpecial(db *gorm.DB, tableName string, MapFieldValue map[string]interface{}, query interface{}, args ...interface{}) error {
	var countNum int64
	db.Table(tableName).Where(query, args).Count(&countNum)
	if countNum > 0 {
		// 更新
		delete(MapFieldValue, "created_at")
		if err := db.Table(tableName).Where(query, args).Updates(MapFieldValue).Error; err != nil {
			return err
		}
		return nil
	} else {
		if err := db.Table(tableName).Create(MapFieldValue).Error; err != nil {
			return err
		}
		return nil
	}
	return nil
}

//// 建表sql语句。通用建表
//func GenerateTableSql(k string, v interface{}) string {
//	var columnType string
//	switch v.(type) {
//	case string:
//		if len(v.(string)) < 32 {
//			columnType = "varchar(32)"
//		} else if len(v.(string)) < 254 {
//			columnType = "varchar(254)"
//		} else {
//			columnType = "longtext"
//		}
//	case int, int8, int16, int32, int64:
//		columnType = "bigint(20)"
//	case float32, float64:
//		columnType = "double"
//	case time.Time:
//		columnType = "datetime"
//	default:
//		columnType = "varchar(32)"
//	}
//
//	return "  `" + k + "` " + columnType + " COMMENT \"\""
//}

// 生成桩基/盖梁等表机构
func GenerateElementCreateSql(reqMap map[string]interface{}, tableName string) createTableSql {
	idx := 0
	lenMaps := len(reqMap)
	elementTableSql := fmt.Sprintf("CREATE TABLE IF NOT EXISTS `%s` ( \n", tableName)
	elementTableSql += "  `globalId` varchar(40) NOT NULL PRIMARY KEY ,\n"
	sa := NewSqlAttributes("", "")
	for k, v := range reqMap {
		if k == "globalId" {
			idx++
			continue
		}
		idx++
		elementTableSql += sa.GenerateTableSql(k, v)
		if idx < lenMaps {
			elementTableSql += ","
		}
		elementTableSql += "\n"
	}
	elementTableSql += ") ENGINE=" + string(sa.dbEngine) + "  AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ; "
	return createTableSql(elementTableSql)
}

type createTableSql string

func (sqlStr createTableSql) ExecCreateSql() error {
	if err := GetMysqlDB().Exec(string(sqlStr)).Error; err != nil {
		log.Error("创建表失败，err:", err.Error())
		return err
	}
	return nil
}

// =============================================
// 自动建表语句
type SqlAttributes struct {
	CreateTableSql string
	PrimaryKey     string
	TableName      string
	dbEngine       dbEngine
}

type dbEngine string

const (
	InnoDB dbEngine = "InnoDB"
	MyISAM dbEngine = "MyISAM"
)

func NewSqlAttributes(primaryKey, tableName string) *SqlAttributes {
	return &SqlAttributes{
		CreateTableSql: "",
		PrimaryKey:     strings.ToLower(primaryKey),
		TableName:      strings.ToLower(tableName),
		dbEngine:       InnoDB,
	}
}

func NewSqlAttributesv1(primaryKey, tableName string, dbE dbEngine) *SqlAttributes {
	return &SqlAttributes{
		CreateTableSql: "",
		PrimaryKey:     strings.ToLower(primaryKey),
		TableName:      strings.ToLower(tableName),
		dbEngine:       dbE,
	}
}

// 生成sql语句
//func (sa *SqlAttributes) GenerateCreateSql(colValMap map[string]interface{}) {
//	idx := 0
//	lenMaps := len(colValMap)
//	sa.CreateTableSql = fmt.Sprintf("CREATE TABLE IF NOT EXISTS `%s` ( \n", sa.TableName)
//	sa.CreateTableSql += "  `" + sa.PrimaryKey + "` " + GenerateTableSqlForCol(colValMap[strings.ToUpper(sa.PrimaryKey)]) + " NOT NULL PRIMARY KEY ,\n"
//	for k, v := range colValMap {
//		k = strings.ToLower(k)
//		if k == sa.PrimaryKey {
//			idx++
//			continue
//		}
//		idx++
//		sa.CreateTableSql += GenerateTableSql(k, v)
//		if idx < lenMaps {
//			sa.CreateTableSql += ","
//		}
//		sa.CreateTableSql += "\n"
//	}
//	sa.CreateTableSql += ") ENGINE=InnoDB  AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ; "
//	return
//}

func (sa *SqlAttributes) GenerateCreateSql(colValMap map[string]interface{}) {
	idx := 0
	lenMaps := len(colValMap)
	sa.CreateTableSql = fmt.Sprintf("CREATE TABLE IF NOT EXISTS `%s` ( \n", sa.TableName)
	primaryKeyList := strings.Split(sa.PrimaryKey, ",")
	if len(primaryKeyList) == 0 || sa.PrimaryKey == "" {
		sa.CreateTableSql += "id INT AUTO_INCREMENT PRIMARY KEY, \n"
		for k, v := range colValMap {
			k = strings.ToLower(k)
			sa.CreateTableSql += "  " + sa.GenerateTableSql(k, v)
			if idx < lenMaps-1 {
				sa.CreateTableSql += ","
			}
			sa.CreateTableSql += "\n"
			idx++
		}
	}
	if len(primaryKeyList) == 1 && sa.PrimaryKey != "" {
		pk := strings.TrimSpace(primaryKeyList[0])
		sa.CreateTableSql += "  `" + pk + "` " + sa.GenerateTableSqlForCol(colValMap[pk], true) + " NOT NULL PRIMARY KEY ,\n"
		delete(colValMap, pk)
		for k, v := range colValMap {
			k = strings.ToLower(k)
			if k == pk {
				idx++
				continue
			}
			sa.CreateTableSql += "  " + sa.GenerateTableSql(k, v)
			if idx < lenMaps-2 {
				sa.CreateTableSql += ","
			}
			sa.CreateTableSql += "\n"
			idx++
		}
	}
	if len(primaryKeyList) > 1 {
		for k, v := range colValMap {
			k = strings.ToLower(k)
			sa.CreateTableSql += "  " + sa.GenerateTableSql(k, v)
			if idx < lenMaps {
				sa.CreateTableSql += ","
			}
			sa.CreateTableSql += "\n"
			idx++
		}
		sa.CreateTableSql += " PRIMARY KEY ("
		for i, pk := range primaryKeyList {
			sa.CreateTableSql += pk
			if i < len(primaryKeyList)-1 {
				sa.CreateTableSql += ", "
			}
		}
		sa.CreateTableSql += ")\n"
	}
	sa.CreateTableSql += ") ENGINE=" + string(sa.dbEngine) + " AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ; "
	return
}

// 建表列类型判断
func (sa *SqlAttributes) GenerateTableSqlForCol(v interface{}, isPrimary bool) string {
	var columnType string
	switch v.(type) {
	case string:
		if sa.dbEngine == MyISAM && !isPrimary {
			columnType = "text"
		} else {
			if len(v.(string)) < 255 {
				columnType = "varchar(255)"
			} else {
				columnType = "longtext"
			}
		}
	case int, int8, int16, int32, int64:
		columnType = "bigint(20)"
	case float32, float64:
		columnType = "double"
	case time.Time:
		columnType = "datetime"
	default:
		columnType = "longtext"
	}

	return columnType
}

// 生成sql 列语句
func (sa *SqlAttributes) GenerateTableSql(colName string, colValue interface{}) string {
	return "  `" + colName + "` " + sa.GenerateTableSqlForCol(colValue, false) + " COMMENT \"\""
}

// 执行建表语句
func (sa *SqlAttributes) execCreateSql(db *gorm.DB) error {
	if err := db.Exec(sa.CreateTableSql).Error; err != nil {
		log.Error("创建表失败，err:", err.Error())
		return err
	}
	return nil
}

// 根据 map 创建表
func (sa *SqlAttributes) ExecCreateTable(colValMap map[string]interface{}, db *gorm.DB) error {
	sa.GenerateCreateSql(ConversionMapKeyToLower(colValMap))
	return sa.execCreateSql(db)
}

// 根据 结构体 创建表
func (sa *SqlAttributes) ExecCreateTableByXmlStru(colValStru interface{}, db *gorm.DB) error {
	colValMap, err := xml2.XmlUnmashal2Map(colValStru)
	if err != nil {
		return err
	}

	sa.GenerateCreateSql(ConversionMapKeyToLower(colValMap))
	return sa.execCreateSql(db)
}

// 根据 结构体 创建表
func (sa *SqlAttributes) ExecCreateTableByJsonStru(colValStru interface{}, db *gorm.DB) error {
	colValStruByte, err := json.Marshal(colValStru)
	if err != nil {
		return err
	}

	var colValMap map[string]interface{}
	if err = json.Unmarshal(colValStruByte, &colValMap); err != nil {
		return err
	}

	sa.GenerateCreateSql(ConversionMapKeyToLower(colValMap))
	return sa.execCreateSql(db)
}

func getColumnType(v interface{}) string {
	out := ""
	switch v := reflect.ValueOf(v); v.Kind() {
	case reflect.String:
		out = "varchar(254)"
	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
		out = "bigint(20)"
	case reflect.Float32, reflect.Float64:
		out = "bigint(20)"
	default:
		out = "varchar(254)"
	}
	return out
}

//=============================================
